package jsyf.oa.dao.business;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONObject;

import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;

import jsyf.oa.entity.business.BusinessMainEntity;
import jsyf.oa.idao.BaseDao;
import jsyf.oa.ientity.IBaseEntity;

@Repository
public class BusinessMainDao extends BaseDao {

    public BusinessMainDao() {
        super.iBaseEntity = new BusinessMainEntity();
    }

    public List<BusinessMainEntity> getEntityListByCondition(String condition, Object[] params) {
        List<JSONObject> jsonList = getJSONListByCondition(condition, params);
        List<BusinessMainEntity> entityList = new ArrayList<>();
        for (int i = 0; i < jsonList.size(); i++) {
            BusinessMainEntity entity = (BusinessMainEntity) convertJson2Entity(jsonList.get(i));
            entityList.add(entity);
        }
        return entityList;
    }

    public BusinessMainEntity getEntityById(String id) {
        return (BusinessMainEntity) super.getIBaseEntityById(id);
    }

    @Override
    public IBaseEntity convertJson2Entity(JSONObject json) {
        return JSON.toJavaObject(json, BusinessMainEntity.class);
    }

    public List<JSONObject> getJSONListByPage(String userId, int requestPage, int pageSize, String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\t*,\tCASE auditFlag\n" +
                "WHEN '-1' THEN\n" +
                "\t1\n" +
                "WHEN '0' THEN\n" +
                "\tCASE currentAuditorId\n" +
                "WHEN '" + userId + "' THEN\n" +
                "\t1\n" +
                "ELSE\n" +
                "\t3\n" +
                "END\n" +
                "WHEN '3' THEN\n" +
                "\tCASE currentAuditorId\n" +
                "WHEN '" + userId + "' THEN\n" +
                "\t2\n" +
                "ELSE\n" +
                "\t3\n" +
                "END\n" +
                "WHEN '1' THEN\n" +
                "\t3\n" +
                "WHEN '4' THEN\n" +
                "\t3\n" +
                "WHEN '2' THEN\n" +
                "\t3\n" +
                "WHEN '5' THEN\n" +
                "\t3\n" +
                "WHEN '6' THEN\n" +
                "\t3\n" +
                "END AS sortNo\n" +
                "FROM\n" +
                "\tv_apl_business_main\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND delFlag = 0\n" +
                "AND (\n" +
                "\tcreateUserId = '"+userId+"'\n" +
                "\tOR trustTrialId='"+userId+"'\n" +
                "\tOR (\n" +
                "\t\tauditFlag <> '-1'\n" +
                "\t\tAND (\n" +
                "\t\t\tid IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_apl_business_audit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tAUDITOR_ID = '"+userId+"'\n" +
                "\t\t\t\tAND auditFlag <> '-1'\n" +
                "\t\t\t)\n" +
                "\t\t\tOR id IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_apl_business_copier\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tCOPY_PERSON_ID = '"+userId+"'\n" +
                "\t\t\t) OR id IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_apl_business_trust_trial\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tTRUST_TRIAL_ID = '"+userId+"'\n" +
                "\t\t\t\tAND TRUST_TRIAL_FLAG <> '-1'\n" +
                "\t\t)\n" +
                "\t\t)\n" +
                "\t)\n" +
                ")\n" );

        if (condition != null) {
            sql.append(condition);
        }
        sql.append("ORDER BY\n" +
                   "\tsortNo,createTime DESC\n");

        super.appendPageSql(sql, requestPage, pageSize);

        List<JSONObject> jsonList = super.queryForJsonList(sql.toString());

        return jsonList;
    }

    public Integer getJSONListByCount(String userId, String condition) {
        StringBuffer sql = new StringBuffer();
        sql.append("SELECT\n" +
                "\tcount(1)\n" +
                "FROM\n" +
                "\tv_apl_business_main\n" +
                "WHERE\n" +
                "\t1 = 1\n" +
                "AND delFlag = 0\n" +
                "AND (\n" +
                "\tcreateUserId = '"+userId+"'\n" +
                "\tOR trustTrialId='"+userId+"'\n" +
                "\tOR (\n" +
                "\t\tauditFlag <> '-1'\n" +
                "\t\tAND (\n" +
                "\t\t\tid IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_apl_business_audit\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tAUDITOR_ID = '"+userId+"'\n" +
                "\t\t\t\tAND auditFlag <> '-1'\n" +
                "\t\t\t)\n" +
                "\t\t\tOR id IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_apl_business_copier\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tCOPY_PERSON_ID = '"+userId+"'\n" +
                "\t\t\t) OR id IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tMAIN_ID\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tt_apl_business_trust_trial\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\tTRUST_TRIAL_ID = '"+userId+"'\n" +
                "\t\t\t\tAND TRUST_TRIAL_FLAG <> '-1'\n" +
                "\t\t)\n" +
                "\t\t)\n" +
                "\t)\n" +
                ")\n" );
        if (condition != null) {
            sql.append(condition);
        }
        sql.append("ORDER BY\n" +
                "\tcreateTime DESC\n");

        String count = super.queryForString(sql.toString());
        return Integer.parseInt(count);
    }

    public Integer getUnDoCount(String userId) {
        StringBuffer sql = new StringBuffer();
        List<Object> sqlArgs = new ArrayList<>();
        sql.append(" SELECT COUNT(1) AS count FROM " + iBaseEntity.getTableName() + " WHERE CURRENT_AUDITOR_ID = ? ");
        sqlArgs.add(userId);
        String count = super.queryForString(sql.toString(), sqlArgs.toArray());
        return Integer.parseInt(count);
    }

    public Integer copierReturn(String id, String copierReturnReason) {
        JSONObject obj = new JSONObject();
        obj.put("ID", id);
        obj.put("COPIER_RETURN_REASON", copierReturnReason);
        obj.put("AUDIT_FLAG", "2");
        obj.put("CURRENT_AUDITOR_ID", "");
        obj.put("CURRENT_AUDITOR_NAME", "");
        JSONObject objId = new JSONObject();
        objId.put("ID", id);
        return super.update("T_APL_BUSINESS_MAIN", obj, objId);
    }

    //查询本公司销售总监和总经理
    public JSONObject getManagerByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" \tSELECT\n" +
                "\t\t\tid AS saleId,\n" +
                "\t\t\tNAME AS salesManager,\n" +
                "\t\t\troleName AS roleName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tcompanyId IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tcompanyId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tv_sys_user\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND id = '" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\t\tAND id IN (\n" +
                "\tSELECT\n" +
                "\t\tUSER_ID\n" +
                "\tFROM\n" +
                "\t\tt_sys_user_2_region\n" +
                "\tWHERE\n" +
                "\t\tREGION_ID IN (\n" +
                "\t\t\tSELECT\n" +
                "\t\t\t\tREGION_ID\n" +
                "\t\t\tFROM\n" +
                "\t\t\t\tt_sys_user_2_region\n" +
                "\t\t\tWHERE\n" +
                "\t\t\t\tUSER_ID = '" + userId + "'\n" +
                "\t\t)\n" +
                ")\n" +
                "\t\tAND roleName = '销售总监'\n" +
                "\t\tAND jobState != 1");
        return super.queryForJsonObject(sql.toString());
    }

    public JSONObject getTopManagerByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" \tSELECT\n" +
                "\t\t\tid AS saleId,\n" +
                "\t\t\tNAME AS salesManager,\n" +
                "\t\t\troleName AS roleName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tcompanyId IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tcompanyId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tv_sys_user\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND id = '" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\tAND roleName = '总经理'\n" +
                "\t\tAND jobState != 1");
        return super.queryForJsonObject(sql.toString());
    }

    public JSONObject getCreditManagerByUserId(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" \tSELECT\n" +
                "\t\t\tid AS id,\n" +
                "\t\t\tNAME AS name,\n" +
                "\t\t\troleName AS roleName\n" +
                "\t\tFROM\n" +
                "\t\t\tv_sys_user\n" +
                "\t\tWHERE\n" +
                "\t\t\tcompanyId IN (\n" +
                "\t\t\t\tSELECT\n" +
                "\t\t\t\t\tcompanyId\n" +
                "\t\t\t\tFROM\n" +
                "\t\t\t\t\tv_sys_user\n" +
                "\t\t\t\tWHERE\n" +
                "\t\t\t\t\t1 = 1\n" +
                "\t\t\t\tAND id = '" + userId + "'\n" +
                "\t\t\t)\n" +
                "\t\tAND roleName = '信控专员'\n" +
                "\t\tAND jobState != 1");
        return super.queryForJsonObject(sql.toString());
    }

    public List<JSONObject> getCopierByUserIds(String userId) {
        StringBuffer sql = new StringBuffer();
        sql.append(" SELECT\n" +
                "\tt_sys_user_2_region.USER_ID AS userId\n" +
                "FROM\n" +
                "\tt_sys_user_2_region LEFT JOIN v_sys_user ON t_sys_user_2_region.USER_ID=v_sys_user.id\n" +
                "  LEFT JOIN t_sys_role_name ON v_sys_user.roleNameId=t_sys_role_name.id\n" +
                "WHERE\n" +
                "\tREGION_ID IN (\n" +
                "\t\tSELECT\n" +
                "\t\t\tREGION_ID\n" +
                "\t\tFROM\n" +
                "\t\t\tt_sys_user_2_region\n" +
                "\t\tWHERE\n" +
                "\t\t\tt_sys_user_2_region.USER_ID = '" + userId + "'\n" +
                "\t) \n" +
                "  AND t_sys_role_name.SUPPORT_FLAG=1 AND v_sys_user.main=1\n" +
                "  AND v_sys_user.roleName in(\n" +
                "SELECT SUPPORT_ROLE_NAME from t_sys_support_role_name_2_role_name\n" +
                "LEFT JOIN v_sys_user ON v_sys_user.roleName=t_sys_support_role_name_2_role_name.ROLE_NAME\n" +
                "WHERE v_sys_user.id='" + userId + "' \n" +
                "AND t_sys_support_role_name_2_role_name.TYPE=0)\n" +
                "GROUP BY t_sys_user_2_region.USER_ID");
        return super.queryForJsonList(sql.toString());
    }


}

